/**
 ****************************************************************************
 * Copyright (C) Marcelo F. Ochoa. All rights reserved.                      *
 * ------------------------------------------------------------------------- *
 * This software is published under the terms of the Apache Software License *
 * version 1.1, a copy of which has been included  with this distribution in *
 * the LICENSE file.                                                         *
 */

package com.prism.demo.i;

import java.io.StringReader;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Enumeration;

import oracle.jdbc.driver.OracleDriver;
import oracle.sql.CLOB;
import oracle.xdb.XMLType;
import oracle.xml.parser.v2.DOMParser;
import oracle.xml.sql.query.OracleXMLQuery;

import com.prism.toolkit.Jowa;
import com.prism.toolkit.JowaArray;
import com.prism.toolkit.JowaImage;
import com.prism.toolkit.JowaUtil;
import com.prism.toolkit.JwpgDocLoad;
import com.prism.toolkit.Jxtp;

/**
 * DBPrism 2.1.1 production / Cocoon 2 Demo Page. With Java Stored Procedures support<BR> 
 * <BR>
 * Modified: 4/Nov/2003 by <a href="mailto:pyropunk@usa.net">Alexander Graesser</a> (LXG)<BR>
 * Changes : <UL><LI>Added log4j logging</LI>
 *           <LI>JavaDoc cleanup</LI>
 *           <LI>code cleanup</LI></UL>
 * Modified: 16/Nov/2005 by <a href="mailto:mochoa@ieee.org">Marcelo F. Ochoa</a> (LXG)<BR>
 *           <LI>Update to new release</LI>
 *           <LI>Change base dir of examples</LI>
 */
class DEMOj {
    public static void STARTUP() {
        Jxtp.prolog("1.0");
        Jxtp.tagOpen("page");
        Jxtp.tag("title", "DBPrism 2.1.1 production / Cocoon 2 Demo Page");
        Jxtp.tag("para", "With Java Stored Procedures support");
        Jxtp.tag("para", "Use Jxtp toolkit and Oracle XML Utility for Java 9.0.2+");
        Jxtp.tag("para", "iso-8859-1 text \u00e1\u00e9\u00ed\u00f3\u00fa");
        Jxtp.tagOpen("table");
        Jxtp.tagOpen("tr");
        Jxtp.tagOpen("td");
        Jxtp.tag("a", "Hello World", "href=\"DEMOj.hello\"");
        Jxtp.tagClose("td");
        Jxtp.tag("td", "Shows overloading stored procedures support (version 1)");
        Jxtp.tagClose("tr");
        Jxtp.tagOpen("tr");
        Jxtp.tag("td", "");
        Jxtp.tagOpen("td");
        Jxtp.tag("a", "Version 2 (two named arguments)", "href=\"DEMOj.hello?name=Marcelo&amp;n=3\"");
        Jxtp.tagClose("td");
        Jxtp.tagClose("tr");
        Jxtp.tagOpen("tr");
        Jxtp.tag("td", "");
        Jxtp.tagOpen("td");
        Jxtp.tag("a", "Version 3 (two simple arguments and one multivalued)", "href=\"DEMOj.hello?name=Marcelo&amp;n=3&amp;msgs=linea1&amp;msgs=linea2\"");
        Jxtp.tagClose("td");
        Jxtp.tagClose("tr");
        Jxtp.tagOpen("tr");
        Jxtp.tagOpen("td");
        Jxtp.tag("a", "Hello World with cookies", "href=\"DEMOj.cookie\"");
        Jxtp.tagClose("td");
        Jxtp.tag("td", "Same as Hello World but with Cookies definition");
        Jxtp.tagClose("tr");
        Jxtp.tagOpen("tr");
        Jxtp.tagOpen("td");
        Jxtp.tag("a", "Hello World with cache control", "href=\"DEMOj.expires\"");
        Jxtp.tagClose("td");
        Jxtp.tag("td", "Same as Hello World but with Cache-Control header");
        Jxtp.tagClose("tr");
        Jxtp.tagOpen("tr");
        Jxtp.tagOpen("td");
        Jxtp.tag("a", "Redirect support", "href=\"DEMOj.redirect\"");
        Jxtp.tagClose("td");
        Jxtp.tag("td", "Redirect to xml.apache.org");
        Jxtp.tagClose("tr");
        Jxtp.tagOpen("tr");
        Jxtp.tag("td", "");
        Jxtp.tagOpen("td");
        Jxtp.tag("a", "Using cocoon redirection", "href=\"DEMOj.action_redirect\"");
        Jxtp.tagClose("td");
        Jxtp.tagClose("tr");
        Jxtp.tagOpen("tr");
        Jxtp.tagOpen("td");
        Jxtp.tag("a", "DBPrism used as Cocoon Actions", "href=\"DEMOj.status?xsl=samples\"");
        Jxtp.tagClose("td");
        Jxtp.tag("td", "Simple hello page using DBPrism as Action and Generator.");
        Jxtp.tagClose("tr");
        Jxtp.tagOpen("tr");
        Jxtp.tag("td", "");
        Jxtp.tagOpen("td");
        Jxtp.tag("a", "using xsl with 'page' value", "href=\"DEMOj.status?xsl=page\"");
        Jxtp.tagClose("td");
        Jxtp.tagClose("tr");
        Jxtp.tagOpen("tr");
        Jxtp.tag("td", "");
        Jxtp.tagOpen("td");
        Jxtp.tag("a", "using xsl with 'xml' value", "href=\"DEMOj.status?xsl=xml\"");
        Jxtp.tagClose("td");
        Jxtp.tagClose("tr");
        Jxtp.tagOpen("tr");
        Jxtp.tagOpen("td");
        Jxtp.tag("a", "Not Authorized support", "href=\"DEMOj.notauthorized\"");
        Jxtp.tagClose("td");
        Jxtp.tag("td", "Sent Not Authorized");
        Jxtp.tagClose("tr");
        Jxtp.tagOpen("tr");
        Jxtp.tagOpen("td");
        Jxtp.tag("a", "Excel Output", "href=\"DEMOj.excel\"");
        Jxtp.tagClose("td");
        Jxtp.tag("td", "Query using SQLJ translator, uses Excel Stylesheet");
        Jxtp.tagClose("tr");
        Jxtp.tagOpen("tr");
        Jxtp.tagOpen("td");
        Jxtp.tag("a", "Flexible parameters support", "href=\"!DEMOj.flex?x=1&amp;y=2&amp;x=3\"");
        Jxtp.tagClose("td");
        Jxtp.tag("td", "Calls the stored procedures with this arguments x=1&amp;y=2&amp;x=3");
        Jxtp.tagClose("tr");
        Jxtp.tagOpen("tr");
        Jxtp.tagOpen("td");
        Jxtp.tag("a", "Input Form Demo", "href=\"DEMOj.input\"");
        Jxtp.tagClose("td");
        Jxtp.tag("td", "Form example with form image support");
        Jxtp.tagClose("tr");
        Jxtp.tagOpen("tr");
        Jxtp.tagOpen("td");
        Jxtp.tag("a", "Input Form Demo", "href=\"DEMOj.textarea\"");
        Jxtp.tagClose("td");
        Jxtp.tag("td", "Form example for CLOB argument support");
        Jxtp.tagClose("tr");
        Jxtp.tagOpen("tr");
        Jxtp.tagOpen("td");
        Jxtp.tag("a", "Query Example using Oracle XSU", "href=\"DEMOj.list\"");
        Jxtp.tagClose("td");
        Jxtp.tag("td", "Calls Oracle XML Query Utility to make the XML result");
        Jxtp.tagClose("tr");
        Jxtp.tagOpen("tr");
        Jxtp.tagOpen("td");
        Jxtp.tag("a", "Query Example using Jxtp", "href=\"DEMOj.list_jxtp\"");
        Jxtp.tagClose("td");
        Jxtp.tag("td", "Query using standard server side JDBC and printing with Jxtp toolkit to compare performance with Oracle XML Query toolkit");
        Jxtp.tagClose("tr");
        Jxtp.tagOpen("tr");
        Jxtp.tagOpen("td");
        Jxtp.tag("a", "Upload Form", "href=\"DEMOj.upload_form\"");
        Jxtp.tagClose("td");
        Jxtp.tag("td", "Upload/Download functionality, stores in the content table");
        Jxtp.tagClose("tr");
        Jxtp.tagOpen("tr");
        Jxtp.tagOpen("td");
        Jxtp.tag("a", "Full qualify support", "href=\"scott.DEMOj.hello\"");
        Jxtp.tagClose("td");
        Jxtp.tag("td", "Using syntax schema.package.stored_procedure");
        Jxtp.tagClose("tr");
        Jxtp.tagOpen("tr");
        Jxtp.tagOpen("td");
        Jxtp.tag("a", "XMLType Download funcionality", "href=\"DEMOj.testXMLType\"");
        Jxtp.tagClose("td");
        Jxtp.tag("td", "Test performance enhanced of transport XMLType over de net");
        Jxtp.tagClose("tr");
        Jxtp.tagClose("table");
        Jxtp.tagClose("page");
    }

    public static void HELLO() {
        Jxtp.prolog("1.0");
        Jxtp.tagOpen("page");
        Jxtp.tag("title", "Hello world");
        Jxtp.tagOpen("content");
        Jxtp.tag("paragraph", "This is my first Prism-Cocoon code, written in Java Stored Procedures!");
        Jxtp.tagClose("content");
        Jxtp.tagClose("page");
        Jxtp.epilog();
    }

    public static void HELLO(String name, int n) {
        Jxtp.prolog("1.0");
        Jxtp.tagOpen("page");
        Jxtp.tag("title", "Hello " + name);
        Jxtp.tagOpen("content");
        for (int i = 0; i < n; i++)
            Jxtp.tag("para", "line number ! i = " + i);
        Jxtp.tagClose("content");
        Jxtp.tagClose("page");
        Jxtp.epilog();
    }

    public static void HELLO(String name, int n, byte[] msgs) {
        Jxtp.prolog("1.0");
        Jxtp.tagOpen("page");
        Jxtp.tag("title", "Hello " + name);
        Jxtp.tagOpen("content");
        for (int i = 0; i < n; i++)
            Jxtp.tag("para", "line number ! i = " + i);
        try {
            JowaArray msgArray = new JowaArray(msgs);
            for (int i = 0; i < msgArray.getSize(); i++)
                Jxtp.tag("para", "msg line = " + i + " " + msgArray.getValueAt(i));
        } catch (Exception e) {
            Jxtp.tag("para", "Error in casting JowaArray argument");
        }
        Jxtp.tagClose("content");
        Jxtp.tagClose("page");
        Jxtp.epilog();
    }

    public static void INPUT() {
        Jxtp.prolog("1.0");
        Jxtp.tagOpen("page");
        Jxtp.tag("title", "Sample Form");
        Jxtp.tagOpen("form", "action='DEMOj.output' method='POST'");
        Jxtp.tag("para", "Please enter a Text String:");
        Jxtp.tag("input", "", "title='Name' type='hidden' name='b' value='default'");
        Jxtp.tag("input", "", "title='Name' type='text' name='b'");
        Jxtp.tag("input", "", "type='image' name='OK' src='icons/ehome.gif'");
        Jxtp.tagClose("form");
        Jxtp.tagClose("page");
    }

    public static void TEXTAREA() {
        Jxtp.prolog("1.0");
        Jxtp.tagOpen("page");
        Jxtp.tag("title", "Sample Text Area Form");
        Jxtp.tagOpen("form", "action='DEMOj.OUTPUT_TEXTAREA' method='POST'");
        Jxtp.tag("para", "Please enter a Huge Text String:");
        Jxtp.tag("textarea", "", "title='Text1' ROWS='12' COLS='40' name='b'");
        Jxtp.tag("br","");
        Jxtp.tag("input", "", "type='image' name='OK' src='icons/ehome.gif'");
        Jxtp.tagClose("form");
        Jxtp.tagClose("page");
    }

    public static void DOWNLOAD() {
        String scriptName = Jowa.GETCGIVAR("SCRIPT_NAME") + Jowa.GETCGIVAR("PATH_INFO");
        JwpgDocLoad.DOWNLOADFILE(scriptName.substring(scriptName.indexOf(Jowa.GETCGIVAR("DOC_ACCESS_PATH")) +
            Jowa.GETCGIVAR("DOC_ACCESS_PATH").length() + 1));
    }

    public static void DOCS_LOAD() throws SQLException {
        Connection sqlconn = (new OracleDriver()).defaultConnection();
        ResultSet rset = null;
        Statement stmt = null;
        Jxtp.prolog("1.0");
        Jxtp.tagOpen("page");
        Jxtp.tag("title", "Documents Uploaded");
        Jxtp.tagOpen("table");
        // Create a Statement
        stmt = sqlconn.createStatement();
        // Select the lobs
        rset = stmt.executeQuery("select name,mime_type,doc_size from " + Jowa.GETCGIVAR("DOCUMENT_TABLE"));
        while (rset.next()) {
            Jxtp.tagOpen("tr");
            String name = rset.getString(1);
            Jxtp.tagOpen("td");
            Jxtp.tag("a", name, "href=\"/dpls/java/" + Jowa.GETCGIVAR("DOC_ACCESS_PATH") + "/" + name + "\"");
            Jxtp.tagClose("td");
            Jxtp.tag("td", rset.getString(2));
            Jxtp.tag("td", rset.getInt(3) + " bytes");
            Jxtp.tagClose("tr");
        }
        Jxtp.tagClose("table");
        rset.close();
        stmt.close();
        Jxtp.tagClose("page");
    }

    public static void UPLOAD_FORM() {
        Jxtp.prolog("1.0");
        Jxtp.tagOpen("page");
        Jxtp.tag("title", "Sample Form");
        Jxtp.tagOpen("form", "method='post' action='/dpls/java/DEMOj.upload' enctype='multipart/form-data'");
        Jxtp.tag("para", "Please select a File to Upload:");
        Jxtp.tag("input", "", "type=\"file\" name=\"binaryfiles\"");
        Jxtp.tag("input", "", "type=\"file\" name=\"binaryfiles\"");
        Jxtp.tag("input", "", "type=\"image\" name=\"OK\" src=\"icons/ehome.gif\"");
        Jxtp.tagClose("form");
        Jxtp.tag("a", "Wanna peek at what others have loaded...", "href=\"DEMOj.docs_load\"");
        Jxtp.tagClose("page");
    }

    public static void UPLOAD(byte[] binaryfiles, byte[] OK) throws SQLException {
        String docTbl = Jowa.GETCGIVAR("DOCUMENT_TABLE");
        Connection cn = new OracleDriver().defaultConnection();
        PreparedStatement stmt = null;
        Jxtp.header("text/html");
        Jxtp.tagOpen("html");
        Jxtp.tag("h1", "Hello world");
        Jxtp.tagOpen("h2");
        Jxtp.tag("p", "This is upload procedure, written in Java Stored Procedures!");
        Jxtp.tag("p", "Upload succefull");
        Jxtp.tag("p", "Lenght =" + Jowa.GETCGIVAR("CONTENT_LENGTH"));
        try {
            JowaImage click = new JowaImage(OK);
            Jxtp.tag("p", click.toString());
        } catch (SQLException e) {
            Jxtp.p("Error casting image point arguments");
        }
        try {
            JowaArray msgArray = new JowaArray(binaryfiles);
            stmt = cn.prepareStatement("update " + docTbl + " set name = ? where name = ?");
            for (int i = 0; i < msgArray.getSize(); i++) { // rename uploaded files
                String oldName = msgArray.getValueAt(i);
                String newName = Jowa.GETCGIVAR("REMOTE_USER") + oldName.substring(oldName.indexOf('/'));
                stmt.setString(1, newName);
                stmt.setString(2, oldName);
                stmt.executeUpdate();
                Jxtp.tag("a", "uploaded file: " + newName,
                    "href=\"/dpls/java/" + Jowa.GETCGIVAR("DOC_ACCESS_PATH") + "/" + newName + "\"");
                Jxtp.tag("br", "");
            }
            stmt.close();
        } catch (Exception e) {
            Jxtp.tag("p", "Upload Exception: " + e.toString());
        }
        Jxtp.tagClose("h2");
        Jxtp.tag("a", "Wanna peek at what others have loaded...", "href=\"/xmlj/DEMOj.docs_load\"");
        Jxtp.tagClose("html");
        Jxtp.epilog();
        stmt = null;
    }

    // LXG: removed UnsupportedEncodingException as it is not thrown.
    // public static void OUTPUT(byte[] b, byte[] OK) throws UnsupportedEncodingException {
    public static void OUTPUT(byte[] b, byte[] OK) {
        Jxtp.prolog("1.0");
        Jxtp.tagOpen("page");
        Jxtp.tag("title", "Output Form");
        Jxtp.tagOpen("content");
        Jxtp.tag("para", "This is my second Prism-Cocoon code with Java Stored Procedures!");
        try {
            JowaArray msgArray = new JowaArray(b);
            for (int i = 0; i < msgArray.getSize(); i++)
                Jxtp.tag("para", "msg line = " + i + " " + msgArray.getValueAt(i));
        } catch (Exception e) {
            Jxtp.tag("para", "Error in casting JowaArray argument");
        }
        //Jxtp.tag("para","b ="+b);
        Jxtp.tagOpen("para");
        Jxtp.p("image point coords (x,y) ");
        try {
            JowaImage click = new JowaImage(OK);
            Jxtp.p(click.toString());
        } catch (SQLException e) {
            Jxtp.p("Error casting image point arguments");
        }
        Jxtp.tagOpen("table");
        Enumeration cgiVars = Jowa.GETCGIVARS();
        while (cgiVars.hasMoreElements()) {
            Jxtp.tagOpen("tr");
            String name = (String)cgiVars.nextElement();
            Jxtp.tag("td", name);
            Jxtp.tag("td", JowaUtil.getCgiVar(name));
            Jxtp.tagClose("tr");
        }
        Jxtp.tagClose("table");
        Jxtp.tagClose("para");
        Jxtp.p("Cookies");
        Jxtp.tagOpen("para");
        Jxtp.tagOpen("table");
        Enumeration cookies = Jxtp.getAllCookies();
        while (cookies.hasMoreElements()) {
            Jxtp.tagOpen("tr");
            String name = (String)cookies.nextElement();
            Jxtp.tag("td", name);
            Jxtp.tag("td", Jxtp.getCookie(name));
            Jxtp.tagClose("tr");
        }
        Jxtp.tagClose("table");
        Jxtp.tagClose("para");
        Jxtp.tagClose("content");
        Jxtp.tagClose("page");
    }

    // LXG: removed UnsupportedEncodingException as it is not thrown.
    // public static void OUTPUT_TEXTAREA(CLOB b, byte[] OK) throws UnsupportedEncodingException,SQLException {
    public static void OUTPUT_TEXTAREA(CLOB b, byte[] OK) throws SQLException {
        Jxtp.prolog("1.0");
        Jxtp.tagOpen("page");
        Jxtp.tag("title", "Output Form");
        Jxtp.tagOpen("content");
        Jxtp.tag("para", "This is the output of textarea test.");
        Jxtp.tagOpen("para");
        Jxtp.tag("para","b is temporary "+b.isTemporary());
        // LXG: removed not used.
        // Reader ior = b.characterStreamValue();
        Jxtp.p("image point coords (x,y) ");
        try {
            JowaImage click = new JowaImage(OK);
            Jxtp.p(click.toString());
        } catch (SQLException e) {
            Jxtp.p("Error casting image point arguments");
        }
        Jxtp.tagOpen("table");
        Enumeration cgiVars = Jowa.GETCGIVARS();
        while (cgiVars.hasMoreElements()) {
            Jxtp.tagOpen("tr");
            String name = (String)cgiVars.nextElement();
            Jxtp.tag("td", name);
            Jxtp.tag("td", JowaUtil.getCgiVar(name));
            Jxtp.tagClose("tr");
        }
        Jxtp.tagClose("table");
        Jxtp.tagClose("para");
        Jxtp.p("Cookies");
        Jxtp.tagOpen("para");
        Jxtp.tagOpen("table");
        Enumeration cookies = Jxtp.getAllCookies();
        while (cookies.hasMoreElements()) {
            Jxtp.tagOpen("tr");
            String name = (String)cookies.nextElement();
            Jxtp.tag("td", name);
            Jxtp.tag("td", Jxtp.getCookie(name));
            Jxtp.tagClose("tr");
        }
        Jxtp.tagClose("table");
        Jxtp.tagClose("para");
        Jxtp.tagClose("content");
        Jxtp.tagClose("page");
    }

    public static void COOKIE() {
        Jxtp.header("text/xml", false);
        // Sent a cookie with an experitation equal to now + 1 day
        Jxtp.sendCookie("testjx", "2", new Date(System.currentTimeMillis() + (1000 * 60 * 60 * 24)));
        Jxtp.closeHeader();
        Jxtp.prolog();
        Jxtp.tagOpen("page");
        Jxtp.tag("title", "Hello world with Cookies");
        Jxtp.tagOpen("content");
        Jxtp.tag("paragraph", "This is my third Prism-Cocoon code with cookies, written in Java Stored Procedures!");
        Jxtp.tagClose("content");
        Jxtp.tagClose("page");
        Jxtp.epilog();
    }

    public static void REDIRECT() {
        Jxtp.redirecUrl("http://xml.apache.org/");
    }

    public static void EXPIRES() {
        Jxtp.header("text/xml", false);
        Jxtp.setHeader("Cache-Control", "no-cache");
        Jxtp.closeHeader();
        Jxtp.prolog();
        Jxtp.tagOpen("page");
        Jxtp.tag("title", "Hello world with Cache-Control");
        Jxtp.tagOpen("content");
        Jxtp.tag("paragraph", "This is my third Prism-Cocoon code with Cache-Control Header, written in Java Stored Procedures!");
        Jxtp.tagClose("content");
        Jxtp.tagClose("page");
        Jxtp.epilog();
    }

    public static void NOTAUTHORIZED() {
        Jxtp.notAuthorized("Test Java Adapter");
    }

    public static void STATUS() {
        Jxtp.prolog();
        Jxtp.tagOpen("page");
        Jxtp.tag("title", "Hello world with with parametized header");
        Jxtp.tagOpen("content");
        Jxtp.tag("paragraph", "This is my third Prism-Cocoon code with X-Cocoon-Sitemap header, written in Java Stored Procedures!");
        Jxtp.tagClose("content");
        Jxtp.tagClose("page");
        Jxtp.epilog();
    }

    public static void ACTION(String xsl) {
        Jxtp.header("text/xml", false);
        // Define on the fly Cocoon sitemap parameter named 'my-xsl' with the value of the argument passed
        Jxtp.setHeader("X-Cocoon-Result", "my-xsl="+xsl);
        Jxtp.setHeader("X-Cocoon-Success", "true");
        Jxtp.closeHeader();
        Jxtp.prolog();
        Jxtp.tagOpen("page");
        Jxtp.tag("title", "Hello world with X-Cocoon- header");
        Jxtp.tagOpen("content");
        Jxtp.tag("paragraph", "This is DBPrism-Cocoon code with X-Cocoon-Result header, written in Java Stored Procedures!");
        Jxtp.tagClose("content");
        Jxtp.tagClose("page");
        Jxtp.epilog();
    }

    public static void ACTION_REDIRECT() {
        Jxtp.header("text/xml", false);
        // Redirect to the Sample page using Cocoon's redirection functionality
        Jxtp.setHeader("X-Cocoon-Redirect", "/xmlj/DEMOj.hello; true");
        Jxtp.setHeader("X-Cocoon-Success", "true");
        Jxtp.closeHeader();
        Jxtp.prolog();
        Jxtp.tag("page","");
        Jxtp.epilog();
    }

    public void EXCEL_query() throws SQLException {
        Connection cn = new OracleDriver().defaultConnection();
        ResultSet rset = null;
        Statement stmt = null;
        // Create a Statement
        stmt = cn.createStatement();
        // Select the lobs
        rset = stmt.executeQuery("SELECT id as EmployeeNumber, " + "       last_name as Name, " + "       title as Title, " +
            "       salary as Salary" + "  FROM s_emp");
        Jxtp.tagOpen("ROWSET");
        while (rset.next()) {
            Jxtp.tagOpen("ROW", "empno=\"" + rset.getString(1) + "\"");
            Jxtp.tag("name", rset.getString(2));
            Jxtp.tag("job", rset.getString(3));
            Jxtp.tag("sal", rset.getString(4));
            Jxtp.tagClose("ROW");
        }
        Jxtp.tagClose("ROWSET");
        rset.close();
        rset = null;
        stmt.close();
        stmt = null;
    }

    public static void EXCEL() throws SQLException {
        Jxtp.prolog("1.0");
        //DefaultContext.setDefaultContext(new DefaultContext(new OracleDriver().defaultConnection()));
        DEMOj q = new DEMOj();
        q.EXCEL_query();
    }

    public static void LIST() throws SQLException {
        Connection cn = new OracleDriver().defaultConnection();
        // Create an instance of the OracleXMLQuery handler
        OracleXMLQuery q = new OracleXMLQuery(cn, "SELECT empno as EmployeeNumber, " + "       ename as Name, " +
            "       sal as Salary" + "  FROM emp");
        // Set some XML Generation options
        Jxtp.prolog();
        // Adds DOM tree
        Jxtp.p(q.getXMLDOM().getDocumentElement());
        Jxtp.epilog();
    }

    public static void LIST_JXTP() throws SQLException {
        Connection cn = new OracleDriver().defaultConnection();
        ResultSet rset = null;
        Statement stmt = null;
        Jxtp.prolog("1.0");
        Jxtp.tagOpen("ROWSET");
        int count = 1;
        for (int j = 0; j < 1; j++) {
            // Create a Statement
            stmt = cn.createStatement();
            // Select the lobs
            rset = stmt.executeQuery("SELECT empno as EmployeeNumber, " + "       ename as Name, " +
                "       sal as Salary" + "  FROM emp");
            while (rset.next()) {
                Jxtp.tagOpen("ROW", "NUM='" + count + "'");
                Jxtp.tag("EMPLOYEENUMBER", rset.getString(1));
                Jxtp.tag("NAME", rset.getString(2));
                Jxtp.tag("SALARY", rset.getString(3));
                Jxtp.tagClose("ROW");
                count++;
            }
            rset.close();
            stmt.close();
        }
        Jxtp.tagClose("ROWSET");
        Jxtp.epilog();
    }

    public static void QUOTES1() throws SQLException {
        Connection cn = new OracleDriver().defaultConnection();
        ResultSet rset = null;
        Statement stmt = null;
        Jxtp.prolog("1.0");
        Jxtp.tagOpen("PORTFOLIO");
        int count = 1;
        // Create a Statement
        stmt = cn.createStatement();
        // Select the lobs
        rset = stmt.executeQuery("SELECT * " + " FROM quote");
        while (rset.next()) {
            Jxtp.tagOpen("QUOTE", "NUM='" + count + "'");
            Jxtp.tag("SYMBOL", rset.getString(1));
            Jxtp.tag("PRICE", rset.getString(2));
            Jxtp.tag("CHANGE", rset.getString(3));
            Jxtp.tagClose("QUOTE");
            count++;
        }
        rset.close();
        stmt.close();
        Jxtp.tagClose("PORTFOLIO");
        Jxtp.epilog();
    }

    public static void QUOTES2() throws SQLException {
        Connection cn = new OracleDriver().defaultConnection();
        // Create an instance of the OracleXMLQuery handler
        OracleXMLQuery q = new OracleXMLQuery(cn, "SELECT * " + " FROM quote");
        // Set some XML Generation options
        Jxtp.prolog();
        q.setRowsetTag("PORTFOLIO");
        q.setRowTag("QUOTE");
        q.useUpperCaseTagNames();
        // Adds DOM tree
        Jxtp.p(q.getXMLDOM().getDocumentElement());
        Jxtp.epilog();
    }

    public static void XFORMS(String post_xml) throws Exception {
        DOMParser theParser = new DOMParser();
        theParser.parse(new StringReader(post_xml));
        Jxtp.prolog("1.0");
        Jxtp.tagOpen("page");
        Jxtp.tag("title", "Output Form");
        Jxtp.tagOpen("content");
        Jxtp.tag("para", "This is my xforms parameters support for DBPrism-Cocoon code with Java Stored Procedures!");
        Jxtp.tagOpen("para");
        Jxtp.p("string length "+post_xml.length()+"\n");
        Jxtp.p("<![CDATA[\n");
        Jxtp.p(theParser.getDocument());
        Jxtp.p("\n]]>");
        Jxtp.tagClose("para");
        Jxtp.tagClose("content");
        Jxtp.tagClose("page");
    }

    public static void FLEX(byte[] name_array, byte[] value_array) {
        Jxtp.prolog("1.0");
        Jxtp.tagOpen("page");
        Jxtp.tag("title", "Output Form");
        Jxtp.tagOpen("content");
        Jxtp.tag("para", "This is my flexible parameters support for DBPrism-Cocoon code with Java Stored Procedures!");
        Jxtp.tagOpen("para");
        try {
            JowaArray names = new JowaArray(name_array);
            JowaArray values = new JowaArray(value_array);
            Jxtp.tagOpen("table");
            for (int i = 0; i < names.getSize(); i++) {
                Jxtp.tagOpen("tr");
                Jxtp.tag("td", names.getValueAt(i));
                Jxtp.tag("td", values.getValueAt(i));
                Jxtp.tagClose("tr");
            }
            Jxtp.tagClose("table");
            Jxtp.tag("para", "num_entries =" + names.getSize());
        } catch (SQLException e) {
            Jxtp.p("Error casting array arguments");
        }
        Jxtp.tagClose("para");
        Jxtp.tagClose("content");
        Jxtp.tagClose("page");
    }

    public static void testXMLType() throws SQLException {
        Connection cn = new OracleDriver().defaultConnection();
        ResultSet rset = null;
        Statement stmt = null;
        Jxtp.header("text/xml",true);
        // Create a Statement
        stmt = cn.createStatement();
        // Select the XMLType content
        rset = stmt.executeQuery("select extract(res,'/Resource/Contents') from resource_view where equals_path(res,'/xdbconfig.xml')=1");
        if (rset.next()) {
          XMLType res = (XMLType)rset.getObject(1);
          Jxtp.p(res.getInputStream());
        }
        rset.close();
        stmt.close();
    }
    
    public static void testXMLTypeNative() throws SQLException {
        Connection cn = new OracleDriver().defaultConnection();
        ResultSet rset = null;
        Statement stmt = null;
        Jxtp.header("text/xml",true);
        // Create a Statement
        stmt = cn.createStatement();
        // Select the XMLType content
        rset = stmt.executeQuery("select extract(res,'/Resource/Contents') from resource_view where equals_path(res,'/xdbconfig.xml')=1");
        if (rset.next()) {
          XMLType res = (XMLType)rset.getObject(1);
          JwpgDocLoad.DOWNLOADFILE(res);
        }
        rset.close();
        stmt.close();
    }
    
    public static void main(String[] args) {
      // TODO:  LXG: write test code here.
    }

    public static void debug_main() {
        main(null);
    }
}
